package keshe;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;

import javax.swing.JOptionPane;

public class Teacher implements Dao {
	private String ID;
	private String password;

	public Teacher() {
	}

	public Teacher(String iD, String password) {
		ID = iD;
		this.password = password;
	}

	public String getID() {
		return ID;
	}

	public void setID(String iD) {
		ID = iD;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}
	public static boolean insertStu(String name) throws SQLException {// 插入学生
		if(name!=null){
			String sql="insert into  students (name) values ('"+name+"');" ;
			//在students表中体现
			String sql1="insert into stuid (password) values ('123456');";
			//同时在stuid中显示
			Connection conn=Login.getConnection();//连接数据库的统一方法
			Statement state=conn.createStatement();
			int rs=state.executeUpdate(sql);
			int rs1=state.executeUpdate(sql1);//处理结果
			if(rs>0 && rs1>0)
				return true;
			else
				return false;
			}
		else   return false;
	}
	
	public  static boolean insertScoreJ(int stuno,double java) throws SQLException {
		//  插入Java成绩 
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql="update students set java="+java+"where stuno ="+stuno+";" ;
		//将学生的JAVA成绩加入students表中
		String sql1="insert into java (java) values ('"+java+"');";
		//同时添加进JAVA表
		int rs=state.executeUpdate(sql);
		int rs1=state.executeUpdate(sql1);//处理结果
		if(rs>0 && rs1>0)
			return true;
		else
			return false;
	}
	
	public static boolean insertScoreM(int stuno,double math) throws SQLException {// 插入数学成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql="update students set math="+math+"where stuno ="+stuno+";" ;
		String sql1="insert into math (math) values ('"+math+"');";
		int rs=state.executeUpdate(sql);
		int rs1=state.executeUpdate(sql1);
		if(rs>0 && rs1>0)
			return true;
		else
			return false;
	}
	
	public static boolean insertScoreP(int stuno,double pe) throws SQLException {//插入体育成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql="update students set pe="+pe+"where stuno ="+stuno+";" ;
		String sql1="insert into pe (pe) values ('"+pe+"');";
		int rs=state.executeUpdate(sql);
		int rs1=state.executeUpdate(sql1);
		if(rs>0 && rs1>0)
			return true;
		else
			return false;
	}

	public static double searchJ(int stuno) throws SQLException {//查找Java成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql="select java from students where stuno="+stuno+";";
		ResultSet rs=state.executeQuery(sql);
		double java = 0;
		while(rs.next()) {
			java=rs.getDouble("java");
		}
		return java;
	}
	
	public static double searchM(int stuno) throws SQLException {//查找数学成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql1="select math from students where stuno="+stuno+";";
		ResultSet rs1=state.executeQuery(sql1);
		double math=0;
		while(rs1.next()) {
			math=rs1.getDouble("math");
		}
		return math;
	}
	
	public static double searchP(int stuno) throws SQLException {//查找体育成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql2="select pe from students where stuno="+stuno+";";
		ResultSet rs2=state.executeQuery(sql2);
		double pe=0;
		while(rs2.next()) {
			pe=rs2.getDouble("pe");
		}
		return pe;
	}
	
	public static double searchS(int stuno) throws SQLException {//查找总成绩
		Connection conn=Login.getConnection();
		Statement state=conn.createStatement();
		String sql2="select sum from students where stuno="+stuno+";";
		ResultSet rs2=state.executeQuery(sql2);
		double pe=0;
		while(rs2.next()) {
			pe=rs2.getDouble("sum");
		}
		return pe;
	}
	
	public static boolean insertSum(int stuno) throws SQLException {//插入总成绩
		Connection conn=Login.getConnection();//连接数据库
		Statement state=conn.createStatement();
		double sum=0.0,java=0.0,math=0.0,pe=0.0;//初始化
		java=Teacher.searchJ(stuno);
		math=Teacher.searchM(stuno);
		pe=Teacher.searchP(stuno);//调用已有的方法获得各科成绩
		sum=java+math+pe;//计算总成绩
		DecimalFormat df=new DecimalFormat("#.##");
		String s=df.format(sum);
		double result=Double.parseDouble(s);//将总成绩格式化为小数点后两位
		String strs="update students set sum="+result+"where stuno ="+stuno+";";
		//SQL语句
		int rss=state.executeUpdate(strs);//处理结果
		if(rss>0)
			return true;
		else
			return false;
	}
	
	public static void mohuSerch(String name) throws SQLException {//模糊查找学生
		Connection conn=Login.getConnection();//与数据库建立连接
		Statement state=conn.createStatement();
		String sql="select * from students where name like '%"+name+"%';";
		ResultSet rs=state.executeQuery(sql);	//处理结果
		while(rs.next()) {
			String na=rs.getString("name");    //姓名
			double java=rs.getDouble("java");     //JAVA 
			double math=rs.getDouble("math");     //MATH
			double pe=rs.getDouble("pe");      //PE
			JOptionPane.showMessageDialog(null, "name: "+name+" java: "+
				java+" math: "+math+" pe: "+pe);
		}
	
		//}
		//else  JOptionPane.showMessageDialog(null, "不存在该学生");
	}
}

